--    Program: 		CAPPOIMP_POSITION_HIERARCHY_VERSION.sql
--    Description:  Import PO POSITION HIERARCHY VERSION information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_position_structure_id     number;
   l_pos_structure_versions_id number;
                    		                 		
   cursor cur_per_pos_structure_versions is
      select * from CRST_PER_POS_STRUCTURE_VERSION
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_per_pos_structure_versions loop   	
     l_err_msg_tmp := '';
     
     SELECT PER_POS_STRUCTURE_VERSIONS_S.NEXTVAL 
     INTO l_pos_structure_versions_id
     FROM DUAL;
     
     update CRST_PER_POS_STRUCTURE_VERSION
     set    pos_structure_version_id_n = l_pos_structure_versions_id
     where  pos_structure_version_id = cur.POS_STRUCTURE_VERSION_ID;
     
     l_position_structure_id := null;
     if cur.POSITION_STRUCTURE_ID is not null then
   	   begin
   	   	 select position_structure_id_n 
   	   	 into   l_position_structure_id
         from   crst_per_position_structures
         where  position_structure_id = cur.POSITION_STRUCTURE_ID;
	   exception
	   	 when others then
	   	   l_err_msg_tmp := 'Position Hierarchy Structure '||cur.POSITION_STRUCTURE_ID||' not found'; 	
	   end;	   	
     end if;
     
     begin
       -- import data
       insert into PER_POS_STRUCTURE_VERSIONS
       (
              POS_STRUCTURE_VERSION_ID   ,
              BUSINESS_GROUP_ID          ,
              POSITION_STRUCTURE_ID      ,
              DATE_FROM                  ,
              VERSION_NUMBER             ,
              COPY_STRUCTURE_VERSION_ID  ,
              DATE_TO                    ,
              REQUEST_ID                 ,
              PROGRAM_APPLICATION_ID     ,
              PROGRAM_ID                 ,
              PROGRAM_UPDATE_DATE        ,
              LAST_UPDATE_DATE           ,
              LAST_UPDATED_BY            ,
              LAST_UPDATE_LOGIN          ,
              CREATED_BY                 ,
              CREATION_DATE              ,
              OBJECT_VERSION_NUMBER       
       
       )
       values
       (
              l_pos_structure_versions_id    ,
              CAPBRIMP_MAPPING.GetBGMapping(cur.BUSINESS_GROUP_ID),
              l_position_structure_id       ,
              cur.DATE_FROM                  ,
              cur.VERSION_NUMBER             ,
              cur.COPY_STRUCTURE_VERSION_ID  ,
              cur.DATE_TO                    ,
              cur.REQUEST_ID                 ,
              cur.PROGRAM_APPLICATION_ID     ,
              cur.PROGRAM_ID                 ,
              cur.PROGRAM_UPDATE_DATE        ,
              cur.LAST_UPDATE_DATE           ,
              CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
              cur.LAST_UPDATE_LOGIN          ,
              CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
              cur.CREATION_DATE              ,
              cur.OBJECT_VERSION_NUMBER      
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;
     
	 if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_POSITION_HIERARCHY_VERSION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
